package tmall.company.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;


import common.db.DBUtil;
import tmall.company.dao.CompanyDao;
import tmall.company.entity.CompanyEntity;

public class CompanyDaoImpl implements CompanyDao {
	


	@Override
	public int add(CompanyEntity entity) throws Exception {
		Connection conn = DBUtil.getConn();
		
		//事务
		conn.setAutoCommit(false);
		
		String sql="INSERT INTO tmall_company_t(NAME,ADDRESS,TELEPHONE,BOSS) VALUES (?,?,?,?)";
		PreparedStatement stmt = conn.prepareStatement(sql);
		
		
		stmt.setString(1,entity.getName());
		stmt.setString(2,entity.getAddress());
		stmt.setString(3,entity.getTelephone());
		stmt.setString(4,entity.getBoss());
		
		int count = stmt.executeUpdate();
		conn.commit();
		return count;
	}

	@Override
	public int deleteById(Integer id) throws Exception {
		Connection conn = DBUtil.getConn();
		String sql="UPDATE tmall_company_t SET is_delete=0 WHERE id=?";
		PreparedStatement stmt = conn.prepareStatement(sql);
		stmt.setInt(1,id);
		int count = stmt.executeUpdate();
		return count;
	}

	@Override
	public int deleteById(String id) throws Exception {
		// TODO Auto-generated method stub
		return 0;
	}

	@Override
	public int update(CompanyEntity entity) throws Exception {
		Connection conn = DBUtil.getConn();
		String sql="update tmall_company_t SET NAME=?,ADDRESS=?,TELEPHONE=?,BOSS=? WHERE id=?";
		PreparedStatement stmt = conn.prepareStatement(sql);
		
		stmt.setString(1,entity.getName());
		stmt.setString(2,entity.getAddress());
		stmt.setString(3,entity.getTelephone());
		stmt.setString(4,entity.getBoss());
		
		stmt.setInt(5,entity.getId());
		int count = stmt.executeUpdate();
		return count;
	}

	@Override
	public CompanyEntity findById(Integer id) throws Exception {
		
		return null;
	}

	@Override
	public CompanyEntity findById(String id) throws Exception {
		Connection conn = DBUtil.getConn();
		String sql="SELECT ID AS id,NAME AS name,ADDRESS AS address,TELEPHONE AS telephone,BOSS AS boss from tmall_company_t WHERE ID=? AND IS_DELETE=1";
		PreparedStatement stmt = conn.prepareStatement(sql);
		stmt.setString(1, id);
		ResultSet rs = stmt.executeQuery();
		CompanyEntity entity=new CompanyEntity();
		while(rs.next()){
			entity.setId(rs.getInt("id"));
			entity.setName(rs.getString("name"));
			entity.setAddress(rs.getString("address"));
			entity.setBoss(rs.getString("boss"));
			entity.setTelephone(rs.getString("telephone"));
		}
		return entity;
	}

	@Override
	public List<CompanyEntity> findAll() throws Exception {
		List<CompanyEntity> list=new ArrayList<CompanyEntity>();
		Connection conn = DBUtil.getConn();
		String sql="SELECT ID AS id,NAME AS name,ADDRESS AS address,TELEPHONE AS telephone,BOSS AS boss from tmall_company_t WHERE  IS_DELETE=1";
		
		PreparedStatement stmt = conn.prepareStatement(sql);
		
		ResultSet rs = stmt.executeQuery();
		while(rs.next()){
			CompanyEntity entity=new CompanyEntity();
			entity.setId(rs.getInt("id"));
			entity.setName(rs.getString("name"));
			entity.setAddress(rs.getString("address"));
			entity.setBoss(rs.getString("boss"));
			entity.setTelephone(rs.getString("telephone"));
			list.add(entity);
		}
		return list;
	}

	
	@Override
	public List<CompanyEntity> findWithPage(Integer currentPage, Integer pageSize) throws Exception {
		List<CompanyEntity> list=new ArrayList<CompanyEntity>();
		Connection conn = DBUtil.getConn();
		String sql="SELECT ID AS id,NAME AS name,ADDRESS AS address,TELEPHONE AS telephone, BOSS AS boss FROM tmall_company_t LIMIT ?,?";
		
		PreparedStatement stmt = conn.prepareStatement(sql);
		stmt.setInt(1,(currentPage-1)*3);
		stmt.setInt(2,pageSize);
		ResultSet rs = stmt.executeQuery();
		while(rs.next()){
			CompanyEntity entity=new CompanyEntity();
			entity.setId(rs.getInt("id"));
			entity.setName(rs.getString("name"));
			entity.setAddress(rs.getString("address"));
			entity.setBoss(rs.getString("boss"));
			entity.setTelephone(rs.getString("telephone"));
			list.add(entity);
		}
		return list;
	}
	
	
	
	

}
